/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package serverapplication;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import pack.Course;
import pack.Question;

/**
 *
 * @author shuyi
 */
public class DesktopApplicationFunctionLibrary {
    
    
    /**
     * pre:username,password can not be null. user must be a prof
     * post: Write the UserProfile info retrieved from db.
     * @param username 
     * @param password
     * @param user
     * @return Return ture if login succeeds.
     * //note: prof checked has NOT been added.
     */
    public static boolean checkLogIn(String username, String password, UserProfile user) {
        boolean success = false;
        DataBaseConnectionSource.databaseLock.lock();
        try {
            if (DataBaseConnectionSource.conn == null) {
                DataBaseConnectionSource.conn = DataBaseConnectionSource.getConnection();
            }
            String query = "SELECT * "
                    + "FROM users "
                    + "WHERE username LIKE ? AND password LIKE ?";
            PreparedStatement stat;
            stat = DataBaseConnectionSource.conn.prepareStatement(query);
            stat.setString(1, username);
            stat.setString(2, password);
            stat.execute();
            ResultSet result = stat.getResultSet();
            if (result.next()) {
                success = true;
                user.user_id = result.getInt("id");
                user.username = result.getString("username");
                user.firstName = result.getString("first_name");
                user.lastName = result.getString("last_name");
            }
            result.close();
            stat.close();
        } catch (SQLException ex) {
            Logger.getLogger(DesktopApplicationFunctionLibrary.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DataBaseConnectionSource.databaseLock.unlock();
        }
        return success;
    }

    /**
     * this function checks whether user has an own tables stores all the answers under his votes.
     * if the table did not exist, then dynamicly create a table for him
     * the table's name format is : user_id+"_"+"answer"
     */
    public static void checkUserHasAnswerTable(UserProfile user){
        
    }
    
    
    /**
     * pre: The user has logged in, A UserProfile has been added.
     * post: 
     * @param user_id get all the course under this id from DB
     * @return Return a course list in type of arraylist<course> from DB
     */
    public static ArrayList<Course> getCourseList(int user_id) throws SQLException {
        String query = "SELECT id, title, body "
                + "FROM courses "
                + "WHERE user_id = ?";
        ArrayList<Course> courseList = new ArrayList<Course>();
        DataBaseConnectionSource.databaseLock.lock();
        try {
            PreparedStatement stat = DataBaseConnectionSource.conn.prepareStatement(query);
            stat.setString(1, Integer.toString(user_id));
            stat.execute();
            ResultSet result = stat.getResultSet();
            while (result.next()) {
                int course_id = result.getInt(1);
                String title = result.getString(2);
                String body = result.getString(3);
                if (body == null) {
                    body = "";
                }
                if (title != null && body != null) {
                    courseList.add(new Course(course_id, title, body));
                } else {
                    System.out.println("null poitner!!");
                }
            }
            result.close();
            stat.close();
        } finally {
            DataBaseConnectionSource.databaseLock.unlock();
        }
        return courseList;
    }

    /**
     * pre: The user has selected a course.
     * post: Question list is selected by course id
     * @param course
     * @return the question list of the course
     */
    public static ArrayList<Question> getQuestionList(Course course){
        ArrayList<Question> questionList=null;
        return questionList;
    }
    
    /**
     * pre: Created Time should not be null.
     * post: add a new question to the DB
     * @param question the question is sent by desktop app. Note that this question's id is invalid.
     * @return 
     */
    public static Question createQuestion(String title, String body, int type, int course_id, int user_id) throws SQLException{
        Calendar rightNow = Calendar.getInstance();
        java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time=sdf.format(rightNow.getTime());
        int id = -1;
        
        // insert a new row into DB
        String query = "INSERT INTO questions (title, body, type, course_id, user_id, created, modified) "
                + "values(?, ?, ?, ?, ?, ?, ?)";
        
        DataBaseConnectionSource.databaseLock.lock();
        try{
        PreparedStatement stat = DataBaseConnectionSource.conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        stat.setString(1, title);
        stat.setString(2, body);
        stat.setInt(3, type);
        stat.setInt(4, course_id);
        stat.setInt(5, user_id);
        stat.setString(6, time);
        stat.setString(7, time);
        stat.executeUpdate();

        // get the id from the newly inserted row
        
        ResultSet result = stat.getGeneratedKeys();
        if (result.next()) {
            id = result.getInt(1);
        }
        stat.close();
        result.close();
        }finally{
            DataBaseConnectionSource.databaseLock.unlock();
        }

        Question question = new Question(id, title, body, type, course_id, user_id, rightNow);
        return question;
    }

    public static int createCourse(int user_id, String title, String body) throws SQLException {
        // insert a new row into DB
        String query = "INSERT INTO courses (user_id, title, body) "
                + "values(?, ?, ?)";
        int id = -1;

        DataBaseConnectionSource.databaseLock.lock();
        try {
            PreparedStatement stat = DataBaseConnectionSource.conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stat.setInt(1, user_id);
            stat.setString(2, title);
            stat.setString(3, body);
            stat.executeUpdate();

            // get the id from the newly inserted row
            ResultSet result = stat.getGeneratedKeys();
            if (result.next()) {
                id = result.getInt(1);
            }
            stat.close();
            result.close();
        } finally {
            DataBaseConnectionSource.databaseLock.unlock();
        }
        return id;
    }

    public static void editCourse(int course_id, String title, String body) throws SQLException {
        String query = "UPDATE courses "
                + "SET title = ?, body = ? "
                + "WHERE id = ?";

        DataBaseConnectionSource.databaseLock.lock();
        try {
            PreparedStatement stat = DataBaseConnectionSource.conn.prepareStatement(query);
            stat.setInt(3, course_id);
            stat.setString(1, title);
            stat.setString(2, body);
            stat.execute();
            stat.close();
        } finally {
            DataBaseConnectionSource.databaseLock.unlock();
        }
    }

    public static void removeCourse(int course_id) throws SQLException {
        DataBaseConnectionSource.databaseLock.lock();
        try{
        String query = "DELETE FROM courses "
                + "WHERE id = ?";
        PreparedStatement stat = DataBaseConnectionSource.conn.prepareStatement(query);
        stat.setInt(1, course_id);
        stat.execute();
        stat.close();
        }finally{
            DataBaseConnectionSource.databaseLock.unlock();
        }
    }
}
